package com.rocky.server.service;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import org.apache.log4j.Logger;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import com.rocky.server.database.DBAdapter;

public class MessageService {
	private static Logger logger = Logger.getLogger(MessageService.class);
	
	public JSONArray getLoveWordByCity(String city){
		DBAdapter db = new DBAdapter();
		String sql = "select user.username,user.nickname,user.sex,user.birthday,loveword.message,loveword.createTime from loveword,user where loveword.userid = user.userid and loveword.location_city = '" + city +"'";
		JSONArray jsa = new JSONArray();
		try{
			db.rs = db.stmt.executeQuery(sql);
			while(db.rs.next()){
				JSONObject jso = new JSONObject();
				jso.put("username", db.rs.getString("username"));
				jso.put("nickname", db.rs.getString("nickname"));
				jso.put("sex", db.rs.getInt("sex"));
				jso.put("birthday", db.rs.getInt("birthday"));
				jso.put("time", db.rs.getTimestamp("createTime"));
				jso.put("message", db.rs.getString("message"));
				jsa.put(jso);
			}
		}catch (SQLException e) {
			logger.error(e.toString());
		}catch (JSONException e){
			logger.error(e.toString());
		}finally{
			db.closeConnect();
		}
		return jsa;
	}
	
	public JSONArray getLoveWordByLocationKey(String location_key){
		DBAdapter db = new DBAdapter();
		String sql = "select user.username,user.nickname,user.sex,user.birthday,loveword.message,loveword.createTime from loveword,user where loveword.userid = user.userid and loveword.location_key = '" + location_key+"'";
		JSONArray jsa = new JSONArray();
		try{
			db.rs = db.stmt.executeQuery(sql);
			while(db.rs.next()){
				JSONObject jso = new JSONObject();
				jso.put("username", db.rs.getString("username"));
				jso.put("nickname", db.rs.getString("nickname"));
				jso.put("sex", db.rs.getInt("sex"));
				jso.put("birthday", db.rs.getInt("birthday"));
				jso.put("time", db.rs.getTimestamp("createTime"));
				jso.put("message", db.rs.getString("message"));
				jsa.put(jso);
			}
		}catch (SQLException e) {
			logger.error(e.toString());
		}catch (JSONException e){
			logger.error(e.toString());
		}finally{
			db.closeConnect();
		}
		return jsa;
	}
	
	public boolean storeLoveWord(int userid,String loveword,String location_key,String location_city){
		DBAdapter db = new DBAdapter();
		Timestamp tt=new Timestamp((new Date()).getTime());
		try {
			PreparedStatement pStmt = db.con.prepareStatement("insert into loveword (`userid`,`location_key`,`location_city`,`message`,`createTime`) values (?,?,?,?,?)");
			pStmt.setInt(1, userid);
			pStmt.setString(2, location_key);
			pStmt.setString(3, location_city);
			pStmt.setString(4, loveword);
			pStmt.setTimestamp(5, tt);
			int flag = pStmt.executeUpdate();
			if(flag>0){
				return true;
			}
		} catch (SQLException e) {
			logger.error(e.toString());
		}finally{
			db.closeConnect();
		}
		return false;
	}
	
	/**
	 * 
	 * @param fromUserid
	 * @param toUserid
	 * @param message
	 * @param type 0 = 普通消息 1 = 好友请求消息
	 * @return
	 */
	public boolean storeMessage(int fromUserid,int toUserid,String message,int type){
		DBAdapter db = new DBAdapter();
		Timestamp tt=new Timestamp((new Date()).getTime());
		try {
			PreparedStatement pStmt = db.con.prepareStatement("insert into message (`fromuserid`,`touserid`,`msg`,`time`,`type`) values (?,?,?,?,?)");
			pStmt.setInt(1, fromUserid);
			pStmt.setInt(2, toUserid);
			pStmt.setString(3, message);
			pStmt.setTimestamp(4, tt);
			pStmt.setInt(5, type);
			int flag = pStmt.executeUpdate();
			if(flag>0){
				return true;
			}
		} catch (SQLException e) {
			logger.error(e.toString());
		}finally{
			db.closeConnect();
		}
		return false;
	}
	
	public JSONArray getMetPointMessageList(int userid){
		DBAdapter db = new DBAdapter();
		JSONArray jsa = new JSONArray();
		try{
			db.rs = db.stmt.executeQuery("select user.username,user.nickname,user.sex,message.msg from message,user where message.fromuserid = user.userid and message.touserid = " + userid);
			while(db.rs.next()){
				JSONObject jso = new JSONObject();
				jso.put("username", db.rs.getString("username"));
				jso.put("nickname", db.rs.getString("nickname"));
				jso.put("sex", db.rs.getInt("sex"));
				jso.put("location", db.rs.getString("msg"));
				jsa.put(jso);
			}
		}catch (SQLException e) {
			logger.error(e.toString());
		}catch (JSONException e) {
			logger.error(e.toString());
		} finally {
			db.closeConnect();
		}
		return jsa;
	}
	
	public JSONObject getMessageList(int userid){
		DBAdapter db = new DBAdapter();
		JSONObject jsa = new JSONObject();
		try {
			db.rs = db.stmt.executeQuery("select u.username as username,u.nickname as nickname,msg.msg as message,msg.type as type from message msg,user u where msg.fromuserid = u.userid and msg.touserid = " + userid +" and msg.type=0");
			while(db.rs.next()){
				JSONObject jso = new JSONObject();
				jso.put("username",db.rs.getString("username"));
				jso.put("nickname", db.rs.getString("nickname"));
				jso.put("msg", db.rs.getString("message"));
				jso.put("type", db.rs.getInt("type"));
				if(jsa.has(jso.getString("username"))){
					jsa.getJSONArray(jso.getString("username")).put(jso);
				}else{
					JSONArray jsaOneUser = new JSONArray();
					jsaOneUser.put(jso);
					jsa.put(jso.getString("username"), jsaOneUser);
				}
			}
			db.stmt.executeUpdate("delete from message where touserid = " + userid + " and type = 0");
		} catch (SQLException e) {
			logger.error(e.toString());
		}catch (JSONException e) {
			logger.error(e.toString());
		} finally {
			db.closeConnect();
		}
		return jsa;
	}
}
